﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace JA.Negocios
{
    public static partial class ModelBO
    {
        public static LinkUtilVO getLinkUtil(int idLinkUtil, string link = null)
        {
            LinkUtilVO linkUtilVO = null;
            List<LinkUtilVO> listLinkUtilVOs = getLinkUtils(idLinkUtil, link);
            if (listLinkUtilVOs != null && listLinkUtilVOs.Count > 0)
                linkUtilVO = listLinkUtilVOs[0];
            return linkUtilVO;
        }
        public static List<LinkUtilVO> getLinkUtils(int? idLinkUtil = null, string link = null, bool? ativo = null)
        {
            string where = "WHERE ";
            StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT lku.idLinkUtil AS lku_idLinkUtil, lku.link AS lku_link, lku.url AS lku_url, lku.ativo AS lku_ativo, lku.cadastro AS lku_cadastro, lku.cadastradoPor AS lku_cadastradoPor, lku.edicao AS lku_edicao, lku.editadoPor AS lku_editadoPor ");
            query.AppendLine("FROM linkUtil AS lku ");

            if (idLinkUtil != null)
            {
                query.AppendLine(where + "idLinkUtil=?idLinkUtil ");
                where = "AND ";
            }
            if (link != null)
            {
                query.AppendLine(where + "link LIKE ?link ");
                where = "AND ";
            }
            if (ativo != null)
            {
                query.AppendLine(where + "ativo=" + ((bool)ativo ? "TRUE " : "FALSE "));
                where = "AND ";
            }

            query.AppendLine("ORDER BY link ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            if (idLinkUtil != null) cmd.Parameters.AddWithValue("?idLinkUtil", idLinkUtil);
            if (link != null) cmd.Parameters.AddWithValue("?link", link);

            MySqlDataReader reader = null;
            try
            {
                cmd.Connection.Open();
                reader = cmd.ExecuteReader();

                List<LinkUtilVO> listLinkUtil = new List<LinkUtilVO>();
                while (reader.Read())
                {
                    LinkUtilVO linkUtilVO = LinkUtilVO.CreateLinkUtilVO(
                        idLinkUtil: reader.GetInt32(reader.GetOrdinal("lku_idLinkUtil")),
                        link: reader.GetString(reader.GetOrdinal("lku_link")),
                        url: reader.GetString(reader.GetOrdinal("lku_url")),
                        ativo: reader.GetBoolean(reader.GetOrdinal("lku_ativo")),
                        cadastro: reader.GetDateTime(reader.GetOrdinal("lku_cadastro")),
                        cadastradoPor: reader.GetString(reader.GetOrdinal("lku_cadastradoPor")),
                        edicao: reader.GetDateTime(reader.GetOrdinal("lku_edicao")),
                        editadoPor: reader.GetString(reader.GetOrdinal("lku_editadoPor"))
                        );
                    listLinkUtil.Add(linkUtilVO);
                }
                reader.Close();
                return listLinkUtil;
            }
            catch { throw; }
            finally { cmd.Connection.Close(); }
        }
        public static void setLinkUtil(LinkUtilVO linkUtilVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("UPDATE linkUtil ");
            query.AppendLine("SET link=?link, url=?url, ativo=?ativo, cadastro=?cadastro, cadastradoPor=?cadastradoPor, edicao=?edicao, editadoPor=?editadoPor ");
            query.AppendLine("WHERE idLinkUtil=?idLinkUtil");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?idLinkUtil", linkUtilVO.IdLinkUtil);
            cmd.Parameters.AddWithValue("?link", linkUtilVO.Link);
            cmd.Parameters.AddWithValue("?url", linkUtilVO.Url);
            cmd.Parameters.AddWithValue("?ativo", linkUtilVO.Ativo);
            cmd.Parameters.AddWithValue("?cadastro", linkUtilVO.Cadastro);
            cmd.Parameters.AddWithValue("?cadastradoPor", linkUtilVO.CadastradoPor);
            cmd.Parameters.AddWithValue("?edicao", linkUtilVO.Edicao);
            cmd.Parameters.AddWithValue("?editadoPor", linkUtilVO.EditadoPor);

            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch { throw; }
            finally { cmd.Connection.Close(); }
        }
        public static long newLinkUtil(LinkUtilVO linkUtilVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO linkUtil ");
            query.AppendLine("(link, url, ativo, cadastro, cadastradoPor, edicao, editadoPor) ");
            query.AppendLine("VALUES (?link, ?url, ?ativo, ?cadastro, ?cadastradoPor, ?edicao, ?editadoPor) ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?link", linkUtilVO.Link);
            cmd.Parameters.AddWithValue("?url", linkUtilVO.Url);
            cmd.Parameters.AddWithValue("?ativo", linkUtilVO.Ativo);
            cmd.Parameters.AddWithValue("?cadastro", linkUtilVO.Cadastro);
            cmd.Parameters.AddWithValue("?cadastradoPor", linkUtilVO.CadastradoPor);
            cmd.Parameters.AddWithValue("?edicao", linkUtilVO.Edicao);
            cmd.Parameters.AddWithValue("?editadoPor", linkUtilVO.EditadoPor);

            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                return cmd.LastInsertedId;
            }
            catch { throw; }
            finally { cmd.Connection.Close(); }
        }
        public static void delLinkUtil(LinkUtilVO linkUtilVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("DELETE FROM linkUtil ");
            query.AppendLine("WHERE idLinkUtil=?idLinkUtil ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?idLinkUtil", linkUtilVO.IdLinkUtil);

            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch { throw; }
            finally { cmd.Connection.Close(); }
        }
    }
}
